﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace Commlibrary
{
    public class SQLHelp
    {
        // 数据库连接对象
        private SqlConnection m_oSqlConn = null;
        // SQL文执行对象
        private SqlCommand m_oSqlComm = null;

        // 数据源
        private String m_sDataSource = string.Empty;
        // 用户名
        private String m_sUserName = string.Empty;
        // 密码
        private String m_sPassword = string.Empty;
        // 数据库名
        private String m_sDataBase = string.Empty;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sDataSource"></param>
        /// <param name="sUserName"></param>
        /// <param name="sPassword"></param>
        /// <param name="sDataBase"></param>
        public SQLHelp(String sDataSource, String sUserName, String sPassword, String sDataBase)
        {
            // 数据源
            this.m_sDataSource = sDataSource;
            // 用户名
            this.m_sUserName = sUserName;
            // 密码
            this.m_sPassword = sPassword;
            // 数据库名
            this.m_sDataBase = sDataBase;

            // Test
            // 数据源
            this.m_sDataSource = "(local)";
            // 用户名
            this.m_sUserName = "sa";
            // 密码
            this.m_sPassword = "q1w2e3r4";
            // 数据库名
            this.m_sDataBase = "Stock";
        }

        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <returns></returns>
        public String GetConnString()
        {
            return "server=" + this.m_sDataSource + 
                ";uid=" + this.m_sUserName + 
                ";pwd=" + this.m_sPassword + 
                ";database=" + this.m_sDataBase;
        }

        /// <summary>
        /// 获取数据库连接对象
        /// </summary>
        /// <returns></returns>
        public void GetConn()
        {
            try
            {
                if (this.m_oSqlConn == null)
                {
                    this.m_oSqlConn =
                        new SqlConnection(this.GetConnString());
                    this.m_oSqlConn.Open();
                    this.m_oSqlComm = new SqlCommand();
                    this.m_oSqlComm.Connection = this.m_oSqlConn;
                }
            }
            catch (Exception exp) {
                throw exp;
            }
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        /// <returns></returns>
        public Boolean BeginTran()
        {
            try
            {
                if (this.m_oSqlConn != null &&
                    this.m_oSqlConn.State == ConnectionState.Open)
                {
                    SqlTransaction tran = this.m_oSqlConn.BeginTransaction();
                    this.m_oSqlComm.Transaction = tran;

                    return true;
                }

                return false;
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }

        /// <summary>
        /// 事务回滚
        /// </summary>
        /// <returns></returns>
        public Boolean RollBackTran()
        {
            try
            {
                if (this.m_oSqlConn.State != ConnectionState.Broken &&
                    this.m_oSqlConn.State != ConnectionState.Closed)
                {
                    if (this.m_oSqlComm != null &&
                        this.m_oSqlComm.Transaction != null)
                    {
                        // 事务回滚
                        this.m_oSqlComm.Transaction.Rollback();
                        return true;
                    }
                }

                return false;
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }

        /// <summary>
        /// 事务提交
        /// </summary>
        /// <returns></returns>
        public Boolean CommitTran()
        {
            try
            {
                if (this.m_oSqlConn.State != ConnectionState.Broken &&
                    this.m_oSqlConn.State != ConnectionState.Closed)
                {
                    if (this.m_oSqlComm != null &&
                        this.m_oSqlComm.Transaction != null)
                    {
                        // 事务提交
                        this.m_oSqlComm.Transaction.Commit();
                        return true;
                    }
                }

                return false;
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseConn()
        {
            try
            {
                if (this.m_oSqlConn != null)
                {
                    this.m_oSqlComm.Dispose();
                    this.m_oSqlComm = null;

                    this.m_oSqlConn.Close();
                    this.m_oSqlConn = null;
                }
            }
            catch (Exception)
            { 
            
            }
        }

        /// <summary>
        /// 执行SQL文
        /// </summary>
        /// <param name="sSqlExecute"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(String sSqlExecute)
        {
            try
            {
                this.GetConn();
                this.m_oSqlComm.CommandText = sSqlExecute;
                return this.m_oSqlComm.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }

        public DataTable ExecuteSQLFillDataTable(String sSqlExecute)
        {
            DataTable dtReturn = new DataTable();

            try
            {
                this.GetConn();
                this.m_oSqlComm.CommandText = sSqlExecute;

                // SQL文执行对象
                SqlDataAdapter oSqlAdapter = new SqlDataAdapter(this.m_oSqlComm);
                oSqlAdapter.Fill(dtReturn);
                this.CloseConn();
            }
            catch (Exception exp)
            {
                throw exp;
            }

            return dtReturn;
        }

        public Object ExecuteScalar(String sSqlExecute)
        {
            try
            {
                this.GetConn();
                this.m_oSqlComm.CommandText = sSqlExecute;
                Object oReturn = this.m_oSqlComm.ExecuteScalar();
                this.CloseConn();
                return oReturn;
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }

        public Boolean TestDBConnect()
        {
            try
            {
                //this.GetConn();



                return true;
            }
            catch (Exception)
            {
                return false;
            }

            /*
            string myconn = "server=(local);uid=sa;pwd=q1w2e3r4;database=Stock";
            string mysql = "select * from M_STOCK";
            //SqlConnection conn = new SqlConnection(@"server=222.71.152.237,1433;initial catalog=stock;integrated security=false;pooling=false;User ID=sa;Password=q1w2e3r4");
            //conn.Open();
            SqlConnection myconnection = new SqlConnection(myconn);
            myconnection.Open();
            SqlCommand mycommand = new SqlCommand(mysql, myconnection);
            SqlDataReader myreader = mycommand.ExecuteReader();
            return true;
             * */
        }














    }
}
